package com.zhouyu.exampleboot.service;

import com.zhouyu.exampleboot.entity.UserPoi;
import com.zhouyu.exampleboot.repositry.UserRepository;
import com.zhouyu.exampleboot.util.PoiUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.HttpURLConnection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

@Service
public class PoiService {
    Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    UserRepository userRepository;

    SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 泛型测试
     *
     * @param t
     * @param <T>
     */
    public <T> void helloT(T t) {
        Field[] fields = t.getClass().getFields();
        Field[] declaredFields = t.getClass().getDeclaredFields();
        logger.info("fields:{}", fields.length);
        logger.info("declaredFields:{}", declaredFields.length);
        for (Field feild : declaredFields) {
            String name = feild.getName();
            logger.info("feildName{}", name);
            Class<?> type = feild.getType();
        }

    }

    public void generRateData() {
        List<UserPoi> userList = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            UserPoi user = new UserPoi();
            user.setSex("男");
            user.setName("zhouyu");
            user.setAge(26);
            user.setClassId(6);
            userList.add(user);
        }
        //userRepository.save(userList);
    }

    public List<UserPoi> getUserList() {
        Pageable page =  PageRequest.of(0, 10,null);
        Page<UserPoi> userPage = userRepository.findAll(page);
        List<UserPoi> content = userPage.getContent();
        logger.info("查询数据量是:datatasize:{}", content.size());
        return content;
    }

    public List<String> getFeildName() {
        ArrayList<String> feildLists = new ArrayList<>();
        Field[] declaredFields = UserPoi.class.getDeclaredFields();
        List<Field> fields = Arrays.asList(declaredFields);
        for (Field feild : fields) {
            String name = feild.getName();
            feildLists.add(name);
        }
        feildLists.add("photo");
        return feildLists;
    }

    public void exportExcel() {
        long startTimeInMillis = Calendar.getInstance().getTimeInMillis();
        String dateTime = dateformat.format(new Date());
        String fileName = "E:\\learn\\" + "OutputExcelDemo.xlsx";//.xlsx  .xls
        List<String> feildNames = getFeildName();
        List<UserPoi> userList = getUserList();
        PoiUtils.writeToLocal(fileName, feildNames, userList);
        long endTimeInMillis = Calendar.getInstance().getTimeInMillis();
        logger.info("普通导出耗时：{}秒", (endTimeInMillis - startTimeInMillis) / 1000);
    }

    public void exporExcelFromBroser(HttpServletRequest request, HttpServletResponse response) {
        String dateTime = dateformat.format(new Date());
        String fileName = "下载测试文件" + dateTime + ".xls";
        List<String> feildNames = getFeildName();
        List<UserPoi> userList = getUserList();
        PoiUtils.writeToResponse(request, response, fileName, feildNames, userList);
    }

    public void testExportImg() {
        //创建工作簿
        HttpURLConnection conn1 = null;
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        Sheet imgsheet = workbook.createSheet("imgsheet");
        Drawing patriarch = imgsheet.createDrawingPatriarch();

        //表头
        PoiUtils.createHead(imgsheet, getFeildName(), workbook);

        int rowindex = 2;
        try {
            for (UserPoi user : getUserList()) {
                Row dataRow = imgsheet.createRow(rowindex); //创建行
                dataRow.setHeight((short) (256 * 15));//设置行高

                int cellCursor = 0;
                Cell cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getId() + "");
                cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getName() + "");
                cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getSex() + "");
                cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getAge() + "");
                cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getClassId() + "");
                //插入图片
                cell = dataRow.createCell(cellCursor);
                imgsheet.setColumnWidth(cellCursor, 256 * 25 + 100);//列宽
                String pictureUrl = "https://survey.s3.cn-north-1.amazonaws.com.cn/shenzhen/topicBank/4c6f92dc-cd76-4bed-91e9-25d4daf5abc9/1546051381672.png";
                if (!StringUtils.isEmpty(pictureUrl)) {
                    XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cellCursor, rowindex, (short) cellCursor + 1, rowindex + 1);

                    byte[] bytes = PoiUtils.readInputStremOfImg(pictureUrl);
                    anchor.setAnchorType(ClientAnchor.MOVE_AND_RESIZE);//设置图片随单元移动调整大小
                    patriarch.createPicture(anchor, workbook.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_JPEG));
                }
                rowindex++;
            }
            //输出目的地
            String fileName = "E:\\learn\\" + "OutputExcelDemo0.xlsx";//.xlsx  .xls
            FileOutputStream fileOutputStream = new FileOutputStream(new File(fileName));
            workbook.write(fileOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 优化方案i：
     * 将数据切割分段，依次分页查询不同段的数据，然后写入文件该数据。避免单次查询大量的数据然后集中写入大批量数据。
     */
    public void exportExcelOPtimiaze1() {
        long startTimeInMillis = Calendar.getInstance().getTimeInMillis();
        //查询总的数据量
        int totalSize = (int) userRepository.count();

        //单次10000个数据
        int pagesize = 500000;

        //计算出执行次数
        int execuTimes = totalSize % pagesize > 0 ? totalSize / pagesize + 1 : totalSize / pagesize;

        // 内存中保留 2000 条数据，以免内存溢出，其余写入 硬盘
        SXSSFWorkbook wb = new SXSSFWorkbook(4000);
        // 创建一个Excel的sheet
        Sheet sheet = wb.createSheet("sheet1");


        List<String> feildNames = getFeildName();

        //创建标题
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, feildNames.size()));//表头跨列合并（起始行号，终止行号，起始列，终止列）
        Row row = sheet.createRow(0);
        String title = "2018年调查报表统计报表";
        Cell cell1Title = row.createCell(0);
        cell1Title.setCellValue(title);

        //创建表头
        Row rowHead = sheet.createRow(1);
        for (int k = 0; k < feildNames.size(); k++) {
            Cell cell = rowHead.createCell(k);
            cell.setCellValue(feildNames.get(k));
            cell.setCellStyle(PoiUtils.getColumnTopStyle(wb));//样式
        }


        //分次写入数据
        for (int i = 0; i < execuTimes; i++) {
            Pageable page =  PageRequest.of(i, pagesize);
            Page<UserPoi> userPage = userRepository.findAll(page);
            //List<User> userList = userPage.getContent();
            List<UserPoi> userList = new ArrayList<>(500000);
            userList = userPage.getContent();

            //行数
            int rowSize = userList.size() < pagesize ? userList.size() : pagesize;

            //遍历每行数据
            for (int j = 0; j < rowSize; j++) {
                //开头两个行是占用了，向后挪一下（i * rowSize是每次的起始行）
                Row dataRow = sheet.createRow(i * rowSize + j + 2);
                UserPoi user = userList.get(j);
                int cellCursor = 0; //单元格游标
                Cell cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getId() + "");
                cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getName() + "");
                cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getSex() + "");
                cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getAge() + "");
                cell = dataRow.createCell(cellCursor++);
                cell.setCellValue(user.getClassId() + "");
            }
            // userList.clear();
        }
        FileOutputStream fileOutputStream = null;
        try {
            String fileName = "E:\\learn\\" + "OutputExcelDemo.xls";//.xlsx  .xls
            fileOutputStream = new FileOutputStream(new File(fileName));
            wb.write(fileOutputStream);
        } catch (IOException e) {
            logger.error("导出异常!!!");
        } finally {
            try {
                if (fileOutputStream != null) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                logger.error("关闭流异常!!!");
            }
        }
        long endTimeInMillis = Calendar.getInstance().getTimeInMillis();
        logger.info("优化方案1耗时：{}秒", (endTimeInMillis - startTimeInMillis) / 1000);


    }

    /**
     * 优化方案2：导入到多张sheet
     */
    public void exportExcelOPtimiaze2() {
        //查询总的数据量
        int pageRowNo = 0;
        int rowNum = 0;//总的行号
        int totalSize = (int) userRepository.count();
        SXSSFWorkbook wb = new SXSSFWorkbook(4000);
        if (totalSize > 1000000) {
            Sheet sheet = null;
            while (true) {
                if (totalSize % 500000 == 0) {
                    //每50万切换一张sheet
                    sheet = wb.createSheet("sheet" + totalSize % 500000);
                    sheet = wb.getSheetAt(totalSize / 500000);
                    pageRowNo = 0;
                }
                rowNum++;
                Row row = sheet.createRow(pageRowNo++);


            }
        }

    }


}
